# -*- coding=utf-8 -*-
import urllib.request
import urllib.parse
import re
import time


def query_plant_values(db_engine):
    sql = "SELECT id, value_symbol, value_num FROM p_values WHERE value_symbol IN ('Final.Target Pieces', 'Final.Current Pieces', 'Final.Forecast', 'Assembly LineQ Sum Mor and Late.Planned', 'P_Final.Target_Pieces', 'P_Final.Current_Pieces', 'P_Final.Forecast', 'P_Final.Planned')"
    result = db_engine.execute(sql)
    result_list = []
    for row in result:
        tmp_dict = {}
        for column, value in row.items():
            tmp_dict[column] = value
        result_list.append(tmp_dict)
    return result_list


def query_plant_values_from_archive(db_engine, start_time, end_time):
    id_dict = {
        'q_target': '2935',
        'p_target': '11011',
        'q_current': '3571',
        'p_current': '10989',
        'q_planned': '13675',
        'p_planned': '10979'
    }
    val_dict = {'q_target': 0,
                'p_target': 0,
                'q_current': 0,
                'p_current': 0,
                'q_planned': 0,
                'p_planned': 0}
    for (k, id_val) in id_dict.items():
        sql = "SELECT * FROM (SELECT value_num FROM p_value_archive WHERE value ='%s' AND to_date(time_stamp,'dd.mm.yy hh24:mi:ss') BETWEEN to_date('%s','yyyy-mm-dd hh24:mi:ss') AND to_date('%s','yyyy-mm-dd hh24:mi:ss') ORDER BY id DESC) WHERE rownum=1" % (
            id_val, start_time, end_time)
        result = db_engine.execute(sql)
        for row in result:
            for column, value in row.items():
                val_dict[k] = value
    return val_dict


# 抓取页面数据
def crawl_info(post_data):
    data = urllib.parse.urlencode(post_data).encode('ascii')
    url = 'http://wwipsw1988/baa/jsp/IPS-C/NEP/system_filling/baa_w76_daily_volume.jsp?language=en&country=GB&winId=1&packageUser=apipsc76:IPS_QFC'
    req = urllib.request.urlopen(url, data)
    content = req.read().decode('iso-8859-1')
    total_val_list = re.findall(">(\d+)</td>\\n\\n\\n   </tr>", content, flags=re.S)
    total_val = 0
    if len(total_val_list):
        total_val = total_val_list[0]
    return total_val


# def get_assembly_production(date_str=None):
#     from .util import calc_forecast_num, calc_time_lag, get_date_str
#     q_line_shift_1_start_time = '05:54'
#     q_line_shift_1_end_time = '14:30'
#     q_line_shift_2_start_time = '14:30'
#     q_line_shift_2_end_time = '23:00'
#
#     today_date_str = get_date_str()
#     standard_today_date_str = get_date_str(0, '%Y-%m-%d')
#     tomorrow_date_str = get_date_str(1)
#     # yesterday_date_str = get_date_str(-1)
#
#     if date_str is None:
#         q_date_str = today_date_str
#         p_start_date_str = today_date_str
#         p_end_date_str = tomorrow_date_str
#     else:
#         time_tuple = time.strptime(date_str, '%Y-%m-%d')
#         _date_str = time.strftime('%d.%m.%y', time_tuple)
#         q_date_str = _date_str
#         p_start_date_str = _date_str
#         timestamp = time.mktime(time_tuple) + 24 * 3600
#         next_day_time_tuple = time.localtime(timestamp)
#         p_end_date_str = time.strftime('%d.%m.%y', next_day_time_tuple)
#
#     shift1_start_time_str = today_date_str + ' ' + q_line_shift_1_start_time
#     shift1_end_time_str = today_date_str + ' ' + q_line_shift_1_end_time
#     shift2_start_time_str = today_date_str + ' ' + q_line_shift_2_start_time
#     shift2_end_time_str = today_date_str + ' ' + q_line_shift_2_end_time
#
#     now_timestamp = time.time()
#     now_time_tuple = time.localtime()
#     system_time_str = time.strftime('%Y-%m-%d %H:%M:%S', now_time_tuple)
#     shift1_start_time_tuple = time.strptime(shift1_start_time_str, '%d.%m.%y %H:%M')
#     shift1_start_timestamp = time.mktime(shift1_start_time_tuple)
#     shift1_end_time_tuple = time.strptime(shift1_end_time_str, '%d.%m.%y %H:%M')
#     shift1_end_timestamp = time.mktime(shift1_end_time_tuple)
#
#     shift2_start_time_tuple = time.strptime(shift2_start_time_str, '%d.%m.%y %H:%M')
#     shift2_start_timestamp = time.mktime(shift2_start_time_tuple)
#     shift2_end_time_tuple = time.strptime(shift2_end_time_str, '%d.%m.%y %H:%M')
#     shift2_end_timestamp = time.mktime(shift2_end_time_tuple)
#     shift_num = 0
#     if date_str is None or standard_today_date_str == date_str:
#         shift_num = 2
#         if shift1_start_timestamp <= now_timestamp < shift1_end_timestamp:
#             shift_num = 1
#     shift1_total_second = calc_time_lag(shift1_start_time_str, shift1_end_time_str, '%d.%m.%y %H:%M')
#     period_end_timestamp = shift1_end_timestamp
#     if now_timestamp < shift1_end_timestamp:
#         period_end_timestamp = now_timestamp
#     shift1_passed_second = int(round(period_end_timestamp - shift1_start_timestamp))
#     shift1_passed_second = shift1_passed_second if shift1_passed_second > 0 else 0
#
#     shift2_total_second = calc_time_lag(shift2_start_time_str, shift2_end_time_str, '%d.%m.%y %H:%M')
#     period_end_timestamp = shift2_end_timestamp
#     if now_timestamp < shift2_end_timestamp:
#         period_end_timestamp = now_timestamp
#     shift2_passed_second = int(round(period_end_timestamp - shift2_start_timestamp))
#     shift2_passed_second = shift2_passed_second if shift2_passed_second > 0 else 0
#
#     week_num = int(time.strftime('%U'))
#     if week_num % 2 == 1:
#         p_line_shift_1_start_time = '05:54'
#         p_line_shift_1_end_time = '14:30'
#         p_shift_start_time_str = today_date_str + ' ' + p_line_shift_1_start_time
#         p_shift_start_time_tuple = time.strptime(p_shift_start_time_str, '%d.%m.%y %H:%M')
#         p_shift_start_timestamp = time.mktime(p_shift_start_time_tuple)
#         p_shift_end_time_str = today_date_str + ' ' + p_line_shift_1_end_time
#         p_shift_end_time_tuple = time.strptime(p_shift_end_time_str, '%d.%m.%y %H:%M')
#         p_shift_end_timestamp = time.mktime(p_shift_end_time_tuple)
#         p_shift_total_second = shift1_total_second
#     else:
#         p_line_shift_1_start_time = '14:30'
#         p_line_shift_1_end_time = '23:00'
#         p_shift_start_time_str = today_date_str + ' ' + p_line_shift_1_start_time
#         p_shift_start_time_tuple = time.strptime(p_shift_start_time_str, '%d.%m.%y %H:%M')
#         p_shift_start_timestamp = time.mktime(p_shift_start_time_tuple)
#         p_shift_end_time_str = tomorrow_date_str + ' ' + p_line_shift_1_end_time
#         p_shift_end_time_tuple = time.strptime(p_shift_end_time_str, '%d.%m.%y %H:%M')
#         p_shift_end_timestamp = time.mktime(p_shift_end_time_tuple)
#         p_shift_total_second = shift2_total_second
#
#     period_end_timestamp = p_shift_end_timestamp
#     if now_timestamp < p_shift_end_timestamp:
#         period_end_timestamp = now_timestamp
#     p_passed_second = int(round(period_end_timestamp - p_shift_start_timestamp))
#     p_passed_second = p_passed_second if p_passed_second > 0 else 0
#
#     line_query_list = [{
#         'id': 'qShift1',
#         'radioLineType': "o.zpkt in ('ZQFA_100')",
#         'anfDatum': q_date_str,
#         'anfUhrzeit': q_line_shift_1_start_time,
#         'endDatum': q_date_str,
#         'endUhrzeit': q_line_shift_1_end_time,
#         'baaSubmit': 'SEND'
#     }, {
#         'id': 'qShift2',
#         'radioLineType': "o.zpkt in ('ZQFA_100')",
#         'anfDatum': q_date_str,
#         'anfUhrzeit': q_line_shift_2_start_time,
#         'endDatum': q_date_str,
#         'endUhrzeit': q_line_shift_2_end_time,
#         'baaSubmit': 'SEND'
#     }, {
#         'id': 'pShift1',
#         'radioLineType': "o.zpkt in ('ZPFA_100')",
#         'anfDatum': p_start_date_str,
#         'anfUhrzeit': p_line_shift_1_start_time,
#         'endDatum': p_end_date_str,
#         'endUhrzeit': p_line_shift_1_end_time,
#         'baaSubmit': 'SEND'
#     }]
#
#     result_dict = {'shift_num': shift_num, 'system_time': system_time_str}
#     for line_query_item in line_query_list:
#         item_id = line_query_item['id']
#         del line_query_item['id']
#         result_dict[item_id] = crawl_info(line_query_item)
#     if result_dict['qShift1']:
#         result_dict['qShift1Forecast'] = calc_forecast_num(int(result_dict['qShift1']), shift1_passed_second,
#                                                            shift1_total_second)
#     else:
#         result_dict['qShift1Forecast'] = 0
#
#     if result_dict['qShift2']:
#         result_dict['qShift2Forecast'] = calc_forecast_num(int(result_dict['qShift2']), shift2_passed_second,
#                                                            shift2_total_second)
#     else:
#         result_dict['qShift2Forecast'] = 0
#
#     if result_dict['pShift1']:
#         result_dict['pShift1Forecast'] = calc_forecast_num(int(result_dict['pShift1']), p_passed_second, p_shift_total_second)
#     else:
#         result_dict['pShift1Forecast'] = 0
#
#     return result_dict

def query_assembly_value_from_archive(db_engine, id_val, start_time, end_time):
    sql = "SELECT * FROM (SELECT value_num FROM p_value_archive " \
          "WHERE value ='%s' AND time_stamp >= TO_DATE('%s','dd.mm.yy hh24:mi:ss') " \
          "AND time_stamp < TO_DATE('%s','dd.mm.yy hh24:mi:ss') ORDER BY id DESC) " \
          "WHERE rownum=1" % (id_val, start_time, end_time)
    cur = db_engine.execute(sql)
    num = 0
    row_list = cur.fetchall()
    if len(row_list) > 0:
        num = int(row_list[0][0])
    return num


def query_assembly_values_from_online(db_engine):
    sql = "SELECT id, value_symbol, value_num FROM p_values WHERE value_symbol IN ( 'Final.Current Pieces', 'Final.Forecast', 'Final.Planned', 'Assembly LineQ Sum Mor and Late.Planned', 'Line P 2 shift.Early Shift target', 'Line P 2 shift.Late Shift target', 'P_Final.Current_Pieces', 'P_Final.Forecast', 'P_Final.Planned', 'Line Q 2 shift.Early Shift target', 'Line Q 2 shift.Late Shift target' )"
    result = db_engine.execute(sql)
    result_list = []
    for row in result:
        tmp_dict = {}
        for column, value in row.items():
            tmp_dict[column] = value
        result_list.append(tmp_dict)
    return result_list


def query_hvb_values_from_archive(db_engine, id_val, start_time, end_time):
    sql = "SELECT * FROM (SELECT value_num FROM p_value_archive " \
          "WHERE value ='%s' AND time_stamp >= TO_DATE('%s','dd.mm.yy hh24:mi:ss') " \
          "AND time_stamp < TO_DATE('%s','dd.mm.yy hh24:mi:ss') ORDER BY id DESC) " \
          "WHERE rownum=1" % (id_val, start_time, end_time)
    cur = db_engine.execute(sql)
    num = 0
    row_list = cur.fetchall()
    if len(row_list) > 0:
        num = int(row_list[0][0])
    return num


def query_hvb_values_form_online(db_engine):
    sql = "SELECT id,value_symbol,value_num FROM p_values WHERE value_symbol IN ('MP03_partcount_setpoint','MP03_actual_pieces','MP03_Forecast','MP03_current_target_pieces','BA04_target_parts','BA04_actual_pieces','BA04_Forecast','BA04_current_target_pieces')"
    query_data = db_engine.execute(sql)
    # data_dict = {}
    # for row in query_data:
    #     idx = row[0]
    #     val = row[1]
    #     data_dict[idx] = val
    # return data_dict
    result_list = []
    for row in query_data:
        tmp_dict = {}
        for column, value in row.items():
            tmp_dict[column] = value
        result_list.append(tmp_dict)
    return result_list


def get_assembly_production(db_archive, db_online, shift_dict):
    from .util import get_date_str, calc_forecast_num
    today_date_str = get_date_str()
    shift1_start_time_str = today_date_str + ' ' + shift_dict['shift_1_start_time']
    shift1_end_time_str = today_date_str + ' ' + shift_dict['shift_1_end_time']

    now_timestamp = time.time()
    now_time_tuple = time.localtime()
    system_time_str = time.strftime('%Y-%m-%d %H:%M:%S', now_time_tuple)
    shift1_start_time_tuple = time.strptime(shift1_start_time_str, '%d.%m.%y %H:%M:%S')
    shift1_start_timestamp = time.mktime(shift1_start_time_tuple)
    shift1_end_time_tuple = time.strptime(shift1_end_time_str, '%d.%m.%y %H:%M:%S')
    shift1_end_timestamp = time.mktime(shift1_end_time_tuple)
    shift_total_second = shift_dict['shift_total_second']
    # shift2_start_time_str = today_date_str + ' ' + shift_2_start_time
    # shift2_end_time_str = today_date_str + ' ' + shift_2_end_time
    # shift2_start_time_tuple = time.strptime(shift2_start_time_str, '%d.%m.%y %H:%M:%S')
    # shift2_start_timestamp = time.mktime(shift2_start_time_tuple)

    shift_num = 2
    if shift1_start_timestamp <= now_timestamp < shift1_end_timestamp:
        shift_num = 1

    production_values_online = query_assembly_values_from_online(db_online)

    online_value_dict = {}
    for row in production_values_online:
        idx = row['id']
        online_value_dict[idx] = int(row['value_num'])

    result_dict = {
        'shift_num': shift_num,
        'system_time': system_time_str,
        'q_target_1': online_value_dict[13745],
        'q_current_1': 0,
        'q_forecast_1': 0,
        'q_planned_1': 0,
        'q_target_2': online_value_dict[13746],
        'q_current_2': 0,
        'q_forecast_2': 0,
        'q_planned_2': 0,
        'p_target_1': online_value_dict[20151],
        'p_current_1': 0,
        'p_forecast_1': 0,
        'p_planned_1': 0,
        'p_target_2': online_value_dict[20153],
        'p_current_2': 0,
        'p_forecast_2': 0,
        'p_planned_2': 0,
        'shift_1_start_time': shift_dict['shift_1_start_time'],
        'shift_1_end_time': shift_dict['shift_1_end_time'],
        'shift_2_start_time': shift_dict['shift_2_start_time'],
        'shift_2_end_time': shift_dict['shift_2_end_time']
    }

    # if shift_num > 0:
    #     q1_current_num = query_assembly_value_from_archive(db_archive, '3571', shift1_start_time_str,
    #                                                     shift1_end_time_str)
    #     result_dict['q_current_1'] = q1_current_num
    #     result_dict['q_planned_1'] = query_assembly_value_from_archive(db_archive, '3562', shift1_start_time_str,
    #                                                                 shift1_end_time_str)
    #     # q1_passed_second = now_timestamp - shift1_start_timestamp
    #     # if q1_passed_second < 0:
    #     #     q1_passed_second = 0
    #     # elif q1_passed_second > shift_total_second:
    #     #     q1_passed_second = shift_total_second
    #     # result_dict['q_forecast_1'] = calc_forecast_num(q1_current_num, q1_passed_second,
    #     #                                                 shift_total_second)
    # if shift_num > 1:
    #     q2_current_num = query_assembly_value_from_archive(db_archive, '3571', shift2_start_time_str,
    #                                                     shift2_end_time_str)
    #     result_dict['q_current_2'] = q2_current_num
    #     result_dict['q_planned_2'] = query_assembly_value_from_archive(db_archive, '3562', shift2_start_time_str,
    #                                                                 shift2_end_time_str)
    #     # q2_passed_second = now_timestamp - shift2_start_timestamp
    #     # if q2_passed_second < 0:
    #     #     q2_passed_second = 0
    #     # elif q2_passed_second > shift_total_second:
    #     #     q2_passed_second = shift_total_second
    #     # result_dict['q_forecast_1'] = calc_forecast_num(q2_current_num, q2_passed_second,
    #     #                                                 shift_total_second)

    if shift_num == 1:
        result_dict['q_current_1'] = online_value_dict[3571]
        result_dict['q_forecast_1'] = online_value_dict[3553]
        result_dict['q_planned_1'] = online_value_dict[3562]

        result_dict['p_current_1'] = online_value_dict[10989]
        result_dict['p_forecast_1'] = online_value_dict[10969]
        result_dict['p_planned_1'] = online_value_dict[10979]
    elif shift_num == 2:
        q1_current_num = query_assembly_value_from_archive(db_archive, '3571', shift1_start_time_str, shift1_end_time_str)
        result_dict['q_current_1'] = q1_current_num
        result_dict['q_forecast_1'] = calc_forecast_num(q1_current_num, shift_total_second, shift_total_second)
        result_dict['q_planned_1'] = query_assembly_value_from_archive(db_archive, '3562', shift1_start_time_str,
                                                                    shift1_end_time_str)
        result_dict['q_current_2'] = online_value_dict[3571]
        result_dict['q_forecast_2'] = online_value_dict[3553]
        result_dict['q_planned_2'] = online_value_dict[3562]

        p1_current_num = query_assembly_value_from_archive(db_archive, '10989', shift1_start_time_str, shift1_end_time_str)
        result_dict['p_current_1'] = p1_current_num
        result_dict['p_forecast_1'] = calc_forecast_num(p1_current_num, shift_total_second, shift_total_second)
        result_dict['p_planned_1'] = query_assembly_value_from_archive(db_archive, '10979', shift1_start_time_str,
                                                                    shift1_end_time_str)
        result_dict['p_current_2'] = online_value_dict[10989]
        result_dict['p_forecast_2'] = online_value_dict[10969]
        result_dict['p_planned_2'] = online_value_dict[10979]

    # p1_current_num = query_assembly_value_from_archive(db_archive, '10989', shift1_start_time_str,
    #                                                 shift2_end_time_str)
    # result_dict['p_current_1'] = p1_current_num
    # result_dict['p_planned_1'] = query_assembly_value_from_archive(db_archive, '10979', shift1_start_time_str,
    #                                                             shift2_end_time_str)
    # week_num = int(time.strftime('%U'))
    # if week_num % 2 == 1:
    #     p1_passed_second = now_timestamp - shift1_start_timestamp
    # else:
    #     p1_passed_second = now_timestamp - shift2_start_timestamp
    #
    # if p1_passed_second < 0:
    #     p1_passed_second = 0
    # elif p1_passed_second > shift_total_second:
    #     p1_passed_second = shift_total_second
    # result_dict['p_forecast_1'] = calc_forecast_num(p1_current_num, p1_passed_second,
    #                                                 shift_total_second)
    result_dict['week_num'] = int(time.strftime('%U'))
    return result_dict


def get_hvb_production(db_archive, db_online, shift_dict):
    from .util import get_date_str, calc_forecast_num
    today_date_str = get_date_str()
    shift1_start_time_str = today_date_str + ' ' + shift_dict['shift_1_start_time']
    shift1_end_time_str = today_date_str + ' ' + shift_dict['shift_1_end_time']

    now_timestamp = time.time()
    now_time_tuple = time.localtime()
    system_time_str = time.strftime('%Y-%m-%d %H:%M:%S', now_time_tuple)
    shift1_start_time_tuple = time.strptime(shift1_start_time_str, '%d.%m.%y %H:%M:%S')
    shift1_start_timestamp = time.mktime(shift1_start_time_tuple)
    shift1_end_time_tuple = time.strptime(shift1_end_time_str, '%d.%m.%y %H:%M:%S')
    shift1_end_timestamp = time.mktime(shift1_end_time_tuple)
    shift_total_second = shift_dict['shift_total_second']
    shift_num = 2
    if shift1_start_timestamp <= now_timestamp < shift1_end_timestamp:
        shift_num = 1

    production_values_online = query_hvb_values_form_online(db_online)

    online_value_dict = {}
    for row in production_values_online:
        idx = row['id']
        online_value_dict[idx] = int(row['value_num'])

    result_dict = {
        'shift_num': shift_num,
        'system_time': system_time_str,
        'dm_target_1': 0,
        'dm_current_1': 0,
        'dm_forecast_1': 0,
        'dm_planned_1': 0,
        'battery_target_1': 0,
        'battery_current_1': 0,
        'battery_forecast_1': 0,
        'battery_planned_1': 0,
        'dm_target_2': 0,
        'dm_current_2': 0,
        'dm_forecast_2': 0,
        'dm_planned_2': 0,
        'battery_target_2': 0,
        'battery_current_2': 0,
        'battery_forecast_2': 0,
        'battery_planned_2': 0,
        'shift_1_start_time': shift_dict['shift_1_start_time'],
        'shift_1_end_time': shift_dict['shift_1_end_time'],
        'shift_2_start_time': shift_dict['shift_2_start_time'],
        'shift_2_end_time': shift_dict['shift_2_end_time']
    }

    if shift_num == 1:
        result_dict['dm_target_1'] = online_value_dict[12998]
        result_dict['dm_current_1'] = online_value_dict[13383]
        result_dict['dm_forecast_1'] = online_value_dict[13645]
        result_dict['dm_planned_1'] = online_value_dict[13387]
        result_dict['battery_target_1'] = online_value_dict[13001]
        result_dict['battery_current_1'] = online_value_dict[13384]
        result_dict['battery_forecast_1'] = online_value_dict[13646]
        result_dict['battery_planned_1'] = online_value_dict[13002]
    elif shift_num == 2:
        dm_target_m = query_hvb_values_from_archive(db_archive, '12998', shift1_start_time_str, shift1_end_time_str)
        dm_current_m = query_hvb_values_from_archive(db_archive, '13383', shift1_start_time_str, shift1_end_time_str)
        dm_forecast_m = calc_forecast_num(dm_current_m, shift_total_second, shift_total_second)
        dm_planned_m = query_hvb_values_from_archive(db_archive, '13387', shift1_start_time_str, shift1_end_time_str)
        battery_target_m = query_hvb_values_from_archive(db_archive, '13001', shift1_start_time_str,
                                                         shift1_end_time_str)
        battery_current_m = query_hvb_values_from_archive(db_archive, '13384', shift1_start_time_str,
                                                          shift1_end_time_str)
        battery_forecast_m = calc_forecast_num(battery_current_m, shift_total_second, shift_total_second)
        battery_planned_m = query_hvb_values_from_archive(db_archive, '13002', shift1_start_time_str,
                                                          shift1_end_time_str)

        result_dict['dm_target_1'] = dm_target_m
        result_dict['dm_current_1'] = dm_current_m
        result_dict['dm_forecast_1'] = dm_forecast_m
        result_dict['dm_planned_1'] = dm_planned_m
        result_dict['battery_target_1'] = battery_target_m
        result_dict['battery_current_1'] = battery_current_m
        result_dict['battery_forecast_1'] = battery_forecast_m
        result_dict['battery_planned_1'] = battery_planned_m

        result_dict['dm_target_2'] = online_value_dict[12998]
        result_dict['dm_current_2'] = online_value_dict[13383]
        result_dict['dm_forecast_2'] = online_value_dict[13645]
        result_dict['dm_planned_2'] = online_value_dict[13387]
        result_dict['battery_target_2'] = online_value_dict[13001]
        result_dict['battery_current_2'] = online_value_dict[13384]
        result_dict['battery_forecast_2'] = online_value_dict[13646]
        result_dict['battery_planned_2'] = online_value_dict[13002]
    result_dict['week_num'] = int(time.strftime('%U'))
    return result_dict
